package org.iteam.core.utils;

import java.util.ArrayList;
import java.util.List;

public class SqlBuilder {
	private static final int STATE_FIELD = 0;
	private static final int STATE_WHERE = 1;
	private static final int STATE_COMPLETE = 2;
	public static final int TYPE_INSERT = 1;
	public static final int TYPE_UPDATE = 2;
	public static final int TYPE_DELETE = 3;
	public static final int TYPE_QUERY = 4;
	private StringBuilder sql;
	private List<Object> params;
	private int type;
	private int state = 0;
	public static final int ORDERBY_DESC = 0;
	public static final int ORDERBY_ASC = 1;

	/**
	 * 构造方法，生成SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @param type
	 *            SQL类型
	 */
	public SqlBuilder(String tableName, int type) {
		this.type = type;

		if (type == TYPE_INSERT) {
			this.sql = new StringBuilder("insert into ");
			this.sql.append(tableName);
			this.sql.append(" (");
		} else if (type == TYPE_UPDATE) {
			this.sql = new StringBuilder("update ");
			this.sql.append(tableName);
			this.sql.append(" set ");
		} else if (type == TYPE_DELETE) {
			this.sql = new StringBuilder("delete from ");
			this.sql.append(tableName);
			this.sql.append(' ');
		} else if (type == TYPE_QUERY) {
			this.sql = new StringBuilder("select * from ");
			this.sql.append(tableName);
			this.sql.append(' ');
		}

		this.params = new ArrayList<Object>();
	}

	/**
	 * 构造方法，生成SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @param param
	 *            查询的数据列
	 * @param type
	 *            SQL类型
	 */
	public SqlBuilder(String tableName, String param, int type) {
		this.type = type;

		if (type == TYPE_INSERT) {
			this.sql = new StringBuilder("insert into ");
			this.sql.append(tableName);
			this.sql.append(" (");
		} else if (type == TYPE_UPDATE) {
			this.sql = new StringBuilder("update ");
			this.sql.append(tableName);
			this.sql.append(" set ");
		} else if (type == TYPE_DELETE) {
			this.sql = new StringBuilder("delete from ");
			this.sql.append(tableName);
			this.sql.append(' ');
		} else if (type == TYPE_QUERY) {
			this.sql = new StringBuilder("select ");
			this.sql.append(param);
			this.sql.append(" from ");
			this.sql.append(tableName);
			this.sql.append(' ');
		}

		this.params = new ArrayList<Object>();
	}

	/**
	 * 创建插入SQL的SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 */
	public static SqlBuilder createInsert(String tableName) {
		return new SqlBuilder(tableName, TYPE_INSERT);
	}

	/**
	 * 创建更新SQL的SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 */
	public static SqlBuilder createUpdate(String tableName) {
		return new SqlBuilder(tableName, TYPE_UPDATE);
	}

	/**
	 * 创建删除SQL的SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 */
	public static SqlBuilder createDelete(String tableName) {
		return new SqlBuilder(tableName, TYPE_DELETE);
	}

	/**
	 * 创建查询SQL的SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @return
	 */
	@Deprecated
	public static SqlBuilder createQuery(String tableName) {
		return new SqlBuilder(tableName, TYPE_QUERY);
	}

	/**
	 * 创建查询SQL的SqlBuilder对象
	 * 
	 * @param tableName
	 *            表名
	 * @param param
	 *            查询的数据列
	 * @return
	 */
	public static SqlBuilder createQuery(String tableName, String param) {
		return new SqlBuilder(tableName, param, TYPE_QUERY);
	}

	/**
	 * 增加field值
	 * 
	 * @param fieldName
	 *            列名
	 * @param value
	 *            值
	 */
	public void addField(String fieldName, Object value) {
		if (type != TYPE_DELETE && type != TYPE_QUERY) {
			if (this.state == STATE_FIELD) {
				this.sql.append(fieldName);
				if (this.type == TYPE_INSERT)
					this.sql.append(',');
				else if (this.type == TYPE_UPDATE)
					this.sql.append("=?,");

				this.params.add(value);
			}
		}
	}

	/**
	 * 增加field值，不需要sql补全
	 * 
	 * @param fieldSql
	 * @param value
	 */
	public void addFieldSql(String fieldSql, Object value) {
		if (this.state == STATE_FIELD) {
			this.sql.append(fieldSql);
			this.sql.append(',');
			this.params.add(value);
		}
	}

	/**
	 * 增加field值，不需要sql补全和值
	 * 
	 * @param fieldSql
	 */
	public void addFieldSql(String fieldSql) {
		if (this.state == STATE_FIELD) {
			this.sql.append(fieldSql);
			this.sql.append(',');
		}
	}

	/**
	 * 增加where条件值，在增加完所有的field之后才调用
	 * 
	 * @param fieldName
	 *            列名
	 * @param value
	 *            值
	 */
	public void addWhere(String fieldName, Object value) {
		addWhere(fieldName, value, "=");
	}

	/**
	 * 增加where条件值，在增加完所有的field之后才调用
	 * 
	 * @param fieldName
	 *            列名
	 * @param value
	 *            值
	 * @param compareOperator
	 *            条件符（目前暂时支持“=”，“>”，“<”，“<>”，“>=”，“<=”，“like”）
	 */
	public void addWhere(String fieldName, Object value, String compareOperator) {
		if (this.type != TYPE_INSERT) {
			if (this.state == STATE_FIELD) {
				if (this.type == TYPE_UPDATE)
					this.sql.setCharAt(this.sql.length() - 1, ' ');

				this.sql.append("where 1=1 ");
				this.state = STATE_WHERE;
			}

			if (this.state == STATE_WHERE) {
				this.sql.append("and ");
				this.sql.append(fieldName);
				this.sql.append(" ");
				this.sql.append(compareOperator);
				this.sql.append(" ? ");
				this.params.add(value);
			}
		}
	}

	/**
	 * 添加Where条件语句
	 * 
	 * @param whereSQL
	 */
	public void addWhere(String whereSQL) {
		if (this.type != TYPE_INSERT) {
			if (this.state == STATE_FIELD) {
				if (this.type == TYPE_UPDATE)
					this.sql.setCharAt(this.sql.length() - 1, ' ');

				this.sql.append("where 1=1 ");
				this.state = STATE_WHERE;
			}

			if (this.state == STATE_WHERE) {
				this.sql.append("and (").append(whereSQL).append(") ");
			}
		}
	}

	public void complete() {
		if (this.state != STATE_WHERE && this.state != STATE_COMPLETE) {
			if (this.type == TYPE_INSERT) {
				this.sql.setCharAt(this.sql.length() - 1, ')');
				this.sql.append(" values(");
				for (int i = 0; i < this.params.size(); ++i)
					this.sql.append("?,");

				this.sql.setCharAt(this.sql.length() - 1, ')');
			} else if (this.state == STATE_FIELD) {
				this.sql.setCharAt(this.sql.length() - 1, ' ');
			}
			this.state = STATE_COMPLETE;
		}
	}

	/**
	 * 排序
	 * 
	 * @param fieldName
	 *            列名，可以是多个，列名之间用“,”（英文状态下的逗号）隔开
	 * @param orderby
	 *            顺序
	 */
	public void addOrderBy(String fieldName, int orderby) {
		if (this.type == TYPE_QUERY) {
			if (orderby == 0) {
				this.sql.append("order by ").append(fieldName).append(" desc ");
			} else if (orderby == 1) {
				this.sql.append("order by ").append(fieldName).append(" asc ");
			}
		}
	}

	/**
	 * 返回最大记录数的SQL
	 * 
	 * @return
	 */
	public String getMaxCountSql() {
		complete();
		StringBuilder sb = new StringBuilder("select count(*) as count ");
		String maxCountSql = this.sql.toString();
		int fromString = maxCountSql.indexOf("from", 0);
		int orderby = maxCountSql.indexOf("order by");
		if (orderby != -1) {
			sb.append(maxCountSql.substring(fromString, orderby));
		} else {
			sb.append(maxCountSql.substring(fromString));
		}
		return sb.toString().toUpperCase();
	}

	public String getSql() {
		complete();
		return this.sql.toString().toUpperCase();
	}

	public Object[] getParams() {
		return this.params.toArray();
	}

	/**
	 * 产生按拼音首字母过滤的sql语句
	 * 
	 * @param ch
	 *            输入 'A' ~ 'Z' 的字符
	 * @param fieldName
	 *            比较字段名，字段必须是 varchar 和 char
	 * @return 条件sql语句
	 */
	public static String makePinyinWhere(char ch, String fieldName) {
		char maxChar = ' ';
		char minChar = ' ';
		switch (ch) {
		case 'A':
			maxChar = '驁';
			minChar = '啊';
			break;
		case 'B':
			maxChar = '簿';
			minChar = '驁';
			break;
		case 'C':
			maxChar = '錯';
			minChar = '簿';
			break;
		case 'D':
			maxChar = '鵽';
			minChar = '錯';
			break;
		case 'E':
			maxChar = '樲';
			minChar = '鵽';
			break;
		case 'F':
			maxChar = '鰒';
			minChar = '樲';
			break;
		case 'G':
			maxChar = '腂';
			minChar = '鰒';
			break;
		case 'H':
			maxChar = '夻';
			minChar = '腂';
			break;
		case 'J':
			maxChar = '攈';
			minChar = '夻';
			break;
		case 'K':
			maxChar = '穒';
			minChar = '攈';
			break;
		case 'L':
			maxChar = '鱳';
			minChar = '穒';
			break;
		case 'M':
			maxChar = '旀';
			minChar = '鱳';
			break;
		case 'N':
			maxChar = '桛';
			minChar = '旀';
			break;
		case 'O':
			maxChar = '漚';
			minChar = '桛';
			break;
		case 'P':
			maxChar = '曝';
			minChar = '漚';
			break;
		case 'Q':
			maxChar = '囕';
			minChar = '曝';
			break;
		case 'R':
			maxChar = '鶸';
			minChar = '囕';
			break;
		case 'S':
			maxChar = '蜶';
			minChar = '鶸';
			break;
		case 'T':
			maxChar = '籜';
			minChar = '蜶';
			break;
		case 'W':
			maxChar = '鶩';
			minChar = '籜';
			break;
		case 'X':
			maxChar = '鑂';
			minChar = '鶩';
			break;
		case 'Y':
			maxChar = '韻';
			minChar = '鑂';
			break;
		case 'Z':
			maxChar = '咗';
			minChar = '韻';
			break;
		}

		StringBuilder result = new StringBuilder("CONVERT (left(");
		result.append(fieldName);
		result.append(", 1) USING gbk) BETWEEN '");
		result.append(minChar);
		result.append("' and '");
		result.append(maxChar);
		result.append("'");

		return result.toString();
	}
}
